<?php 
$PrintOutput = '<table style="padding:10px;background-color:#CCC;"><tr bgcolor="#F2F2F2"><td></td><td width="6%"><b>N/Atribuidas</b></td>';

$Group2 = $_POST['Group2'];
$TColums = '';
$LastParent = 0;
$MANTOT = 0;
if ($_POST['Group']  == 'Office') {
	$Group = 'Office';
	$Groupa = 'RefOffice';
	$Gro = 'main_offices';
	$GroVal = '1, 2, 5';
} elseif ($_POST['Group'] == 'User') {
	$Group = 'User';
	$Groupa = 'RefUser';
	$Gro = 'main_users';
	$GroVal = '2';
} 
if ($Group2 == 1) {
	$Field  = 'Ref';
	$Field1 = 'RefProvider';
	$Field2 = 'Provider';
	$Table = 'ccenter_providers';
	$subQuery = "";
} elseif ($Group2 == 2) {
	$Field = 'CODE';
	$Field1 = 'CODE';
	$Field2 = 'CODE';
	$Table = 'ccenter_contas';
	$subQuery = "";
}
# FAZ COLUNAS
$Colums = $GLOBALS['MYD']->db_array("SELECT ref, name AS Colums
			FROM {$Gro} WHERE Type IN ({$GroVal}) ORDER BY ref"); 
$COUNTCOLS = $Colums[0];

for ($f = 1; $f <= $COUNTCOLS; $f++) {
	$TColums .= ','.$Colums[$f]['ref'];
	$TO = preg_split("/,/",$TColums);
	$CTO = count($TO);
	$PrintOutput .= '<td NOWRAP><b>'.$Colums[$f]['Colums'].'</b></td>';
}
$TColums = substr($TColums,1,strlen($TColums)-1);
$PrintOutput .= '</tr>';
### 
# FAZ LINHAS
// Problema com query joins com in
	/*"SELECT 
				ccenter_contas.ParentCODE AS Parent,
				{$Table}.{$Field} AS LineRef,
				{$Table}.name AS Line
				FROM {$Table} 
					INNER JOIN ccenter_invarticles
				GROUP BY LineRef
				ORDER BY LineRef";*/
$Lines = $GLOBALS['MYD']->db_array("SELECT 
		ccenter_contas.ParentCODE AS Parent,
		{$Table}.{$Field} AS LineRef,
		{$Table}.name AS Line
	FROM ccenter_invarticles
	INNER JOIN ccenter_invoices ON RefInvoice = ccenter_invoices.Ref
	LEFT OUTER JOIN ccenter_contas ON ccenter_contas.CODE IN (ccenter_invarticles.CODE, ccenter_invoices.CODE)
	LEFT OUTER JOIN ccenter_providers ON ccenter_providers.Ref IN (ccenter_invarticles.Provider, ccenter_invoices.RefProvider)
	WHERE ccenter_invoices.{$TimeType} BETWEEN '{$_POST['Time']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
	AND {$Table}.{$Field} > 0
	GROUP BY LineRef
	ORDER BY Parent,LineRef");
	$COUNTLINES = $Lines[0];
	
	# LINHAS PAI
	$strParent = '';
	$strRef = '';
	for($i=1;$i<=$COUNTLINES;$i++) {
		$strParent .= $Lines[$i]['Parent'].',';
		$strRef .= $Lines[$i]['LineRef'].',';
	}
	$strParent = substr($strParent,0,strlen($strParent)-1);
	$strRef = substr($strRef,0,strlen($strRef)-1);
	if($strParent == '') {$strParent = 0;}
	if($strRef == '') {$strRef = 0;}
	$Par = $GLOBALS['MYD']->db_array("SELECT 
		name AS Parent,
		CODE AS ParentCODE
	FROM ccenter_contas
	WHERE CODE IN ($strParent)
	ORDER BY CODE"); 
	###
	# FAZ RESULTADOS
		$Res = $GLOBALS['MYD']->db_array("SELECT 
		{$Group} AS GridCol, SUM(a.Amount) AS Results, IF(a.{$Field2}!=0,a.{$Field2},b.{$Field1}) AS GridRow
		FROM ccenter_invarticles AS a
		INNER JOIN ccenter_invoices AS b ON a.RefInvoice = b.Ref
		WHERE b.{$TimeType} BETWEEN '{$_POST['Time']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
		AND a.Status != 5
		AND b.Status = 2
		AND b.Type != 8
		AND (a.{$Group} IN ({$TColums}) OR b.{$Groupa} IN ({$TColums}))
		AND (b.{$Field1} IN ({$strRef}) OR a.{$Field2} IN ({$strRef}))
		GROUP BY GridRow,GridCol
		ORDER BY GridRow,GridCol");
	###
$p = 0;
$r=1;
$c=1;
for ($g = 1; $g <= $COUNTLINES; $g++) { 
	if($g==1) {$lastr=$Res[1]['GridRow'];}
	if ($Lines[$g]['Parent'] != $LastParent) {
		$p++;
		$COLS = $CTO+1;
		$PrintOutput .= '<tr bgcolor="#ECEFDA">
		<td width="100%" colspan="'.$COLS.'"><b>'.$Par[$p]['Parent'].'</b></td></tr>';
		$LastParent = $Lines[$g]['Parent'];
	}
	$PrintOutput .= '<tr bgcolor="#F2F2F2"><td NOWRAP>'.$Lines[$g]['Line'].'</td>';
	if($TColums!=''){
	for ($t = 0; $t < $CTO; $t++) {
		if(isset($Res[$r]) && $Res[$r]['GridCol']==$TO[$t] && $lastr == $Res[$r]['GridRow']){
			$PrintOutput .= '<td align="right">'.$Res[$r]['Results'].'</td>';
			$r++;
		} else {
			$PrintOutput .= '<td></td>';	
		}
		if(isset($Res[$r]) && $t == $CTO-1 && $g <= $COUNTLINES) {
			$lastr = $Res[$r]['GridRow'];
		}
	}
	$PrintOutput .= '</tr>';
	} 
}
$PrintOutput .= '<tr height="1"></tr><tr bgcolor="#F2F2F2"><td><b>Total: </b></td>';
if($TColums!=''){	
	$Res = $GLOBALS['MYD']->db_array("SELECT 
		{$Group} AS GridResult,SUM(a.Amount) AS Results
		FROM ccenter_invarticles AS a
		INNER JOIN ccenter_invoices AS b ON a.RefInvoice = b.Ref
		WHERE b.{$TimeType} BETWEEN '{$_POST['Time']} 00:00:00' AND '{$_POST['TimeEnd']} 23:59:59'
		AND a.Status != 5
		AND b.Status = 2
		AND b.Type != 8
		AND {$Group} IN ({$TColums})
		GROUP BY {$Group}
		ORDER BY {$Group}");
}
$g=1;
for ($t = 0; $t < $CTO; $t++) {
	if(isset($Res[$g]) && $Res[$g]['GridResult']==$TO[$t]){
		$MANTOT += $Res[$g]['Results'];
		$PrintOutput .= '<td align="right"><b>'.$Res[$g]['Results'].'</b></td>';
		$g++;
	} else {
		$PrintOutput .= '<td></td>';	
	}
}
$PrintOutput .= '</tr><tr bgcolor="#F2F2F2"><td width="100%"><b>Total: </b></td><td colspan="100" align="right"><b>'.$MANTOT.'</b></td></tr>
</table>';
echo $PrintOutput;